package com.flexdms.i18n;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.ResourceBundle;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.enterprise.context.ApplicationScoped;
import javax.faces.component.UIViewRoot;
import javax.faces.context.FacesContext;
import javax.inject.Inject;
import javax.transaction.UserTransaction;

import org.apache.deltaspike.core.api.config.ConfigProperty;
import org.apache.deltaspike.core.api.provider.BeanProvider;

@ApplicationScoped
public class I18NMessage
{

	public static final String TABLENAME = "I18NMSGS";
	public static final String KEYCOLUMN_STRING = "MSGKEY";
	public static final String DEFAULT_BUNDLE_NAME = "fxmsgs";

	@Inject
	@ConfigProperty(name = "com.flexdms.i18n.i18nmessage.defaultLocale", defaultValue = "EN")
	String defaultLocaleString;

	@Inject
	LocaleHolder localeHolder;

	public String getDefaultLocale()
	{
		return defaultLocaleString;
	}

	@Inject
	@ConfigProperty(name = "com.flexdms.i18n.i18nmessage.autotranslate", defaultValue = "true")
	boolean autoTranslate;

	@Inject
	Translator translator;

	public Translator getTranslator()
	{
		return translator;
	}

	@Resource(name = "jdbc/dbmsgs")
	protected javax.sql.DataSource dataSource;

	MsgClassLoader msgClassLoader;

	public MsgClassLoader getMsgClassLoader()
	{
		return msgClassLoader;
	}

	public static I18NMessage getDatabaseI18nMessage()
	{
		return BeanProvider.getContextualReference(I18NMessage.class, false);
	}

	@Resource
	UserTransaction tx;

	@PostConstruct
	public void init()
	{
		try
		{
			tx.begin();
			locales.add(stringToLocale(defaultLocaleString));
			msgClassLoader = new MsgClassLoader(this.getClass()
					.getClassLoader());
			String tname = initTable();
			for (String column : getColumns(tname))
			{
				msgClassLoader.addClass(column);
				locales.add(stringToLocale(column));
			}
			tx.commit();
			translator.setSourceLocale(stringToLocale(defaultLocaleString));
		} catch (Exception e)
		{
			try
			{
				tx.rollback();
			} catch (Exception e2)
			{
			}
			throw new RuntimeException(e);
		}
	}

	protected String initTable() throws SQLException
	{
		Connection con = dataSource.getConnection();
		try
		{
			DatabaseMetaData metaData = con.getMetaData();
			String tname = null;
			String types[] = { "TABLE" };
			java.sql.ResultSet rs = metaData.getTables(null, null, null, types);
			while (rs.next())
			{
				String tableName = rs.getString(3);
				if (tableName.equalsIgnoreCase(TABLENAME))
				{
					tname = tableName;
				}
			}
			rs.close();
			if (tname != null)
			{
				return tname;
			}
			if (tname == null)
			{
				Statement statement = con.createStatement();
				String sqlString = "create table " + TABLENAME + "("
						+ KEYCOLUMN_STRING
						+ " varchar(1024) not null primary key, "
						+ toDBColumn(defaultLocaleString) + " varchar(1024) )";
				statement.execute(sqlString);
			}
			rs = metaData.getTables(null, null, null, types);
			while (rs.next())
			{
				String tableName = rs.getString(3);
				if (tableName.equalsIgnoreCase(TABLENAME))
				{
					tname = tableName;
				}
			}
			rs.close();
			return tname;
		} finally
		{
			con.close();
		}
	}

	public List<String> getColumns(String tableName) throws SQLException
	{

		Connection con = dataSource.getConnection();
		try
		{
			List<String> columns = new ArrayList<String>();
			columns = getColumns(tableName, con);
			columns.remove(KEYCOLUMN_STRING);
			columns.remove(defaultLocaleString);
			return columns;
		} finally
		{
			con.close();
		}
	}

	List<Locale> locales = new ArrayList<Locale>();

	public List<Locale> getSupportedLocales()
	{
		return locales;
	}

	public synchronized void addColumn(String newColumn) throws SQLException
	{

		Connection con = dataSource.getConnection();
		try
		{
			Statement statement = con.createStatement();
			statement
					.execute("alter table " + I18NMessage.TABLENAME
							+ " add column " + toDBColumn(newColumn)
							+ " varchar(1024)");
			statement.close();
		} finally
		{
			con.close();
		}
		msgClassLoader.addClass(newColumn);
		locales.add(stringToLocale(newColumn));
		// make resource bundle reload so our new locale is available.
		clearCache();
	}

	public static String getBasename()
	{
		return DBMsgs.class.getName();
	}

	public ResourceBundle loadBundle(Locale locale)
	{
		return ResourceBundle.getBundle(getBasename(), locale, msgClassLoader);
	}

	public ResourceBundle loadBundle()
	{
		return ResourceBundle.getBundle(getBasename(), getCurrentLocale(),
				msgClassLoader);
	}

	public void clearCache()
	{
		ResourceBundle.clearCache(msgClassLoader);
	}

	public boolean isAutoTranslate()
	{
		return autoTranslate;
	}

	public synchronized Map<String, String> loadMessages(String column)
			throws SQLException
	{
		column = toDBColumn(column);

		Map<String, String> msgs = new HashMap<String, String>();
		Connection con = dataSource.getConnection();
		try
		{

			PreparedStatement st = con.prepareStatement("select "
					+ KEYCOLUMN_STRING + ", " + column + " from " + TABLENAME);

			ResultSet rs = st.executeQuery();
			while (rs.next())
			{
				String key = rs.getString(1);
				String value = rs.getString(2);
				// case should not be matter to key
				msgs.put(key.toLowerCase(), value);
			}
			rs.close();
			st.close();
		} finally
		{
			con.close();
		}
		return msgs;
	}

	public synchronized void insertMsg(String key, String msg, String column)
			throws SQLException
	{

		column = toDBColumn(column);
		Connection con = dataSource.getConnection();
		try
		{
			if (column.equalsIgnoreCase(getDefaultLocale()))
			{
				PreparedStatement st = con.prepareStatement("insert into "
						+ TABLENAME + "(" + KEYCOLUMN_STRING + "," + column
						+ ") values (?,?)");

				st.setString(1, key.toLowerCase());
				st.setString(2, msg);
				st.executeUpdate();

				st.close();
			} else
			{
				PreparedStatement st = con.prepareStatement("insert into "
						+ TABLENAME + "(" + KEYCOLUMN_STRING + "," + column
						+ "," + getDefaultLocale() + ") values (?,?, ?)");

				st.setString(1, key.toLowerCase());
				st.setString(2, msg);
				st.setString(3, key);
				st.executeUpdate();

				st.close();
			}

		} finally
		{
			con.close();
		}
	}

	public synchronized void deleteMsg(String key) throws SQLException
	{

		Connection con = dataSource.getConnection();
		try
		{
			PreparedStatement st = con.prepareStatement("delete from "
					+ TABLENAME + " where " + KEYCOLUMN_STRING + "=?");

			st.setString(1, key.toLowerCase());
			st.executeUpdate();

			st.close();
		} finally
		{
			con.close();
		}
	}

	public synchronized void updateMsg(String key, String msg, String column)
			throws SQLException
	{
		column = toDBColumn(column);
		Connection con = dataSource.getConnection();
		try
		{
			PreparedStatement st = con
					.prepareStatement("update " + TABLENAME + " set " + column
							+ " =? where " + KEYCOLUMN_STRING + "=?");

			st.setString(2, key.toLowerCase());
			st.setString(1, msg);
			st.executeUpdate();

			st.close();
		} finally
		{
			con.close();
		}
	}

	public synchronized void updateMsg(List<String> keys, List<String> msgs,
			String column) throws SQLException
	{
		column = toDBColumn(column);
		Connection con = dataSource.getConnection();
		try
		{
			PreparedStatement st = con
					.prepareStatement("update " + TABLENAME + " set " + column
							+ " =? where " + KEYCOLUMN_STRING + "=?");
			Iterator<String> iter1 = keys.iterator();
			Iterator<String> iter2 = msgs.iterator();

			while (iter1.hasNext())
			{
				String key = iter1.next();
				String msg = iter2.next();
				st.setString(2, key.toLowerCase());
				st.setString(1, msg);
				st.executeUpdate();
			}
		} finally
		{
			con.close();
		}
	}

	private static String toDBColumn(String column)
	{
		return column.toUpperCase().replace('-', '_');
	}

	private static List<String> getColumns(String tname, Connection con)
			throws SQLException
	{
		List<String> columns = new ArrayList<String>();
		DatabaseMetaData metaData = con.getMetaData();
		ResultSet rs = metaData.getColumns(null, null, tname, null);
		try
		{
			while (rs.next())
			{
				String column = rs.getString("COLUMN_NAME").toUpperCase();
				columns.add(column);
			}
		} finally
		{
			rs.close();
		}
		return columns;

	}

	public static Locale stringToLocale(String l)
	{
		String partString[] = l.split("_|-");
		if (partString.length == 2)
		{
			return new Locale(partString[0], partString[1]);
		} else if (partString.length == 3)
		{
			return new Locale(partString[0], partString[1], partString[3]);
		} else
		{
			return new Locale(partString[0]);
		}

	}

	public static String localeToString(Locale l)
	{
		String lang = l.getLanguage();
		String country = l.getCountry();
		if (country != null && country.length() > 0)
		{
			return lang + "_" + country;
		} else
		{
			return lang;
		}
	}

	public Locale getCurrentLocale()
	{
		Locale locale = localeHolder.getCurrentLocale();
		if (locale == null)
		{
			return stringToLocale(getDefaultLocale());
		} else
		{
			return locale;
		}
	}
}
